package br.com.hmm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.hmm.businesslogic.SocioService;
import br.com.hmm.entidade.Socio;
import br.com.hmm.entidade.Usuario;

public class SocioDAO {

	private int ultimaIdSocio;
	private Connection conn = null;
	private ResultSet rs = null;
	private static final String INSERT_SOCIO = "INSERT INTO SOCIO (SOC_ID, SOC_NOME, SOC_ENDERECO, SOC_CARGO, SOC_TELEFONE, SOC_EMAIL, SOC_CPF, SOC_USU_ID)  VALUES (NULL,?,?,?,?,?,?,?)";
	private static final String UPDATE_SOCIO = "UPDATE SOCIO SET SOC_NOME = ?, SOC_ENDERECO = ?, SOC_CARGO = ?, SOC_TELEFONE = ?, SOC_EMAIL = ?, SOC_CPF = ? WHERE  SOC_ID = ?;";
	private static final String DELETE_SOCIO = "DELETE FROM CONTA_SOCIO  WHERE  SOCIO_SOC_ID = ?;";
	private static final String FIND_BY_NOME = "SELECT SOC_NOME, SOC_ENDERECO, SOC_CARGO, SOC_TELEFONE, SOC_EMAIL, SOC_CPF, Usuario_USU_ID FROM SOCIO WHERE SOC_NOME LIKE %?% ;";
	private static final String FIND_BY_ID = "SELECT SOC_NOME, SOC_ENDERECO, SOC_CARGO, SOC_TELEFONE, SOC_EMAIL, SOC_CPF FROM SOCIO WHERE Usuario_USU_ID = ?; ";
	private static final String LISTA_SOCIO = "SELECT SOC_NOME, SOC_ENDERECO, SOC_CARGO, SOC_TELEFONE, SOC_EMAIL, SOC_CPF FROM SOCIO ;";
	private static final String LISTA_SOCIO_CONTA = "SELECT c.CON_ID, s.SOC_ID, cs.CONTA_CON_ID, cs.SOCIO_SOC_ID, s.SOC_NOME, s.SOC_ENDERECO, s.SOC_CARGO, s.SOC_TELEFONE, s.SOC_EMAIL, s.SOC_CPF FROM CONTA c, SOCIO s, CONTA_SOCIO cs WHERE c.CON_ID = ? AND c.CON_ID = cs.CONTA_CON_ID AND s.SOC_ID = cs.SOCIO_SOC_ID;";
	private static final String CHECA_CPF = "SELECT SOC_ID , SOC_CPF FROM SOCIO WHERE SOC_CPF = ?";

	public SocioDAO() {

	}

	public boolean inserir(Socio socio) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(INSERT_SOCIO,	PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, socio.getNome());
			stmt.setString(2, socio.getEndereco());
			stmt.setString(3, socio.getCargo());
			stmt.setString(4, socio.getTelefone());
			stmt.setString(5, socio.getEmail());
			stmt.setString(6, socio.getCpf());
			stmt.setInt(7, socio.getId());
			if (checarCPF(socio.getCpf())) {
				return false;
			}
			if (stmt.executeUpdate() > 0) {
				rs = stmt.getGeneratedKeys();
				rs.next();
				setUltimaIdSocio(rs.getInt(1));
				return true;
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}

	public boolean atualizar(Socio socio) throws SQLException {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(UPDATE_SOCIO);
			stmt.setString(1, socio.getNome());
			stmt.setString(2, socio.getEndereco());
			stmt.setString(3, socio.getCargo());
			stmt.setString(4, socio.getTelefone());
			stmt.setString(5, socio.getEmail());
			stmt.setString(6, socio.getCpf());
			stmt.setInt(7, socio.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;

	}

	public boolean delete(Socio socio) throws SQLException {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(DELETE_SOCIO);
			stmt.setInt(1, socio.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;

	}

	public Socio findById(int id) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FIND_BY_ID);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.first() == true) {
				Socio s = new Socio();
				s.setNome(rs.getString(1));
				s.setEndereco(rs.getString(2));
				s.setCargo(rs.getString(3));
				s.setTelefone(rs.getString(4));
				s.setEmail(rs.getString(5));
				s.setCpf(rs.getString(6));
				return s;
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt, rs);
		}

		return null;

	}

	public List<Socio> list() throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(LISTA_SOCIO);
			rs = stmt.executeQuery();
			List<Socio> ls = new ArrayList<Socio>();
			while (rs.next()) {
				Socio s = new Socio();
				s.setNome(rs.getString(1));
				s.setEndereco(rs.getString(2));
				s.setCargo(rs.getString(3));
				s.setTelefone(rs.getString(4));
				s.setEmail(rs.getString(5));
				s.setCpf(rs.getString(6));
				ls.add(s);
			}
			if (!ls.isEmpty()) {
				return ls;
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt, rs);

		}

		return null;
	}

	public List<Socio> listSocioPorConta(int idConta) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(LISTA_SOCIO_CONTA);
			stmt.setInt(1, idConta);
			rs = stmt.executeQuery();
			List<Socio> ls = new ArrayList<Socio>();
			while (rs.next()) {
				Socio s = new Socio();
				s.setId(rs.getInt("SOCIO_SOC_ID"));
				s.setNome(rs.getString("SOC_NOME"));
				s.setEndereco(rs.getString("SOC_ENDERECO"));
				s.setCargo(rs.getString("SOC_CARGO"));
				s.setTelefone(rs.getString("SOC_TELEFONE"));
				s.setEmail(rs.getString("SOC_EMAIL"));
				s.setCpf(rs.getString("SOC_CPF"));
				ls.add(s);
			}
			if (!ls.isEmpty()) {
				return ls;
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt, rs);

		}

		return null;
	}

	public int getUltimaIdSocio() {
		return ultimaIdSocio;
	}

	public void setUltimaIdSocio(int ultimaIdSocio) {
		this.ultimaIdSocio = ultimaIdSocio;
	}

	private boolean checarCPF(String cpf) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs=null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(CHECA_CPF);
			stmt.setString(1, cpf);
			rs = stmt.executeQuery();
			if(rs.next()){
				return true;
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}

}